package com.slipper.system.codeless.service;

import java.util.*;
import java.util.stream.Collectors;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

import com.google.gson.reflect.TypeToken;
import com.slipper.constants.Constants;
import com.slipper.core.cache.CacheKey;
import com.slipper.core.cache.CacheUtil;
import com.slipper.common.service.CommonService;
import com.slipper.core.database.IResolver;
import com.slipper.core.database.ResolverFactory;
import com.slipper.core.database.vo.FieldVO;
import com.slipper.core.database.vo.IndexFieldVO;
import com.slipper.core.database.vo.IndexVO;
import com.slipper.core.database.vo.TableVO;
import com.slipper.exception.CommonException;
import com.slipper.system.codeless.bo.*;
import com.slipper.system.codeless.dao.DataModelDao;
import com.slipper.util.BeanUtil;
import com.slipper.util.DatabaseUtil;
import com.slipper.util.Tool;
import com.slipper.util.UUID;
import com.slipper.system.codeless.dao.DataFieldDao;
import com.slipper.core.transaction.ReadTransactional;
import com.slipper.core.transaction.WriteTransactional;

import org.springframework.stereotype.Service;
import jakarta.annotation.Resource;

@Service
@ReadTransactional
public class DataFieldService {
    @Resource
    private CommonService commonService;
    @Resource
    private DataFieldDao dataFieldDao;
    @Resource
    private DataFieldRelevanceService dataFieldRelevanceService;
    @Resource
    private DataModelDao dataModelDao;

    private final Map<String, DataFieldBO> defaultFieldMap = new HashMap<>() {{
        put("id", new DataFieldBO("id", "主键id", "1", "字符", 20, 0));
        put("update_id", new DataFieldBO("update_id", "更新人id", "0", "字符", 20, 9990));
        put("update_name", new DataFieldBO("update_name", "更新人姓名", "0", "字符", 50, 9991));
        put("update_time", new DataFieldBO("update_time", "更新时间", "0", "时间", 0, 9992));
        put("create_id", new DataFieldBO("create_id", "创建人id", "0", "字符", 20, 9995));
        put("create_name", new DataFieldBO("create_name", "创建人姓名", "0", "字符", 50, 9996));
        put("create_time", new DataFieldBO("create_time", "创建时间", "0", "时间", 0, 9997));
    }};

    private final List<String> reserveFieldList = new ArrayList<>() {{
        add("sql");
        add("matchMode".toLowerCase());
        add("sortField".toLowerCase());
        add("sortType".toLowerCase());
        add("inputParamList".toLowerCase());
        add("manual");
    }};

    public DataFieldBO queryBy(String id) {
        DataFieldBO dataFieldBO = dataFieldDao.queryBy(id);
        dataFieldBO.setRelevanceList(dataFieldRelevanceService.queryFieldId(id));
        return dataFieldBO;
    }

    public DataFieldBO queryByFieldName(String modelId, String fieldName) {
        return dataFieldDao.queryByFieldName(modelId, fieldName);
    }

    public List<DataFieldBO> queryList(DataFieldBO bo) {
        return dataFieldDao.queryList(bo);
    }

    public List<DataFieldBO> queryListBy(String modelId) {
        List<DataFieldBO> dataFieldList = CacheUtil.get(
                CacheKey.DataField.MODEL_LIST_PREFIX + modelId,
                new TypeToken<List<DataFieldBO>>(){}.getType());

        if (dataFieldList == null) {
            DataFieldBO dataFieldQO = new DataFieldBO();
            dataFieldQO.setModelId(modelId);
            dataFieldList = dataFieldDao.queryList(dataFieldQO);
            dataFieldList.forEach(field -> field.setRelevanceList(dataFieldRelevanceService.queryFieldId(field.getId())));

            CacheUtil.set(CacheKey.DataField.MODEL_LIST_PREFIX + modelId, dataFieldList, 60 * 60);
        }
        return dataFieldList;
    }

    public PageInfo<DataFieldBO> queryPage(DataFieldBO bo) {
        PageHelper.startPage(bo.getPageNumber(), bo.getPageSize());
        return new PageInfo<>(dataFieldDao.queryList(bo));
    }

    @WriteTransactional
    public int save(DataFieldBO bo) {
        int result = 0;
        if (Tool.isNotBlank(bo.getId())) {
            bo.setUpdateTime(System.currentTimeMillis());
            result += dataFieldDao.update(bo);
        } else {
            bo.setId(UUID.getUUID());
            bo.setUpdateTime(System.currentTimeMillis());
            bo.setCreateTime(System.currentTimeMillis());
            result += dataFieldDao.insert(bo);
        }

        result += dataFieldRelevanceService.save(bo.getId(), bo.getRelevanceList(), bo.getCreateId());

        CacheUtil.remove(CacheKey.DataField.MODEL_LIST_PREFIX + bo.getModelId());
        return result;
    }

    @WriteTransactional
    public int save(DataModelBO bo) {
        int result = switch (bo.getType()) {
            case Constants.DataModel.TYPE_TABLE -> this.saveTable(bo);
            case Constants.DataModel.TYPE_VIEW -> this.saveView(bo);
            case Constants.DataModel.TYPE_LOCAL_TABLE -> this.saveLocalTable(bo);
            case null, default -> throw new CommonException("模型类型参数不正确！");
        };

        CacheUtil.remove(CacheKey.DataField.MODEL_LIST_PREFIX + bo.getId());
        return result;
    }

    @WriteTransactional
    public int saveTable(DataModelBO bo) {
        boolean isCreate = false;
        String tableName = bo.getCode();
        if (commonService.queryTotal(getResolver().tableTotalSQL(tableName)) <= 0) {
            // 表不存在，创建表
            this.checkField(bo);
            this.addBaseField(bo.getId(), bo.getFieldList());
            this.createDmTable(tableName, bo.getFieldList());
            isCreate = true;
        } else {
            // 表存在，添加不存在的字段，修改存在的字段
            String columnSql = getResolver().columnSQL(tableName);
            List<Map<String, Object>> columnList = commonService.queryList(columnSql);
            for (DataFieldBO dataField : bo.getFieldList()) {
                if (Tool.isNotBlank(dataField.getId()) || DataFieldBO.FIELD_TYPE_DERIVE.equals(dataField.getFieldType())) continue;

                boolean addField = true;
                boolean updateField = false;
                for(Map<String, Object> column : columnList) {
                    if (dataField.getFieldName().equalsIgnoreCase(String.valueOf(column.get("name")))) {
                        addField = false;
                        if (!getResolver().isEqualsType(dataField.getFieldType(), String.valueOf(column.get("type")))
                                || !isEqualsLength(dataField, column)) {
                            updateField = true;
                            break;
                        }
                    }
                }
                if (addField) {
                    addDmField(tableName, dataField);
                } else if (updateField) {
                    DataFieldBO oldDataField = BeanUtil.copy(dataField, DataFieldBO.class);
                    oldDataField.setFieldType(null);
                    updateDmField(tableName, dataField, oldDataField);
                }
            }
        }

        // 删除多余/修改变动的字段
        DataFieldBO qo = new DataFieldBO();
        qo.setModelId(bo.getId());
        qo.setBuiltin(Constants.NO);
        List<DataFieldBO> dataFieldList = dataFieldDao.queryList(qo);

        for (DataFieldBO persist : dataFieldList) {
            boolean hasField = false;
            for(DataFieldBO dataField : bo.getFieldList()) {
                if (DataFieldBO.FIELD_TYPE_DERIVE.equals(dataField.getFieldType())) continue;

                if (Tool.isNotBlank(dataField.getId()) && dataField.getId().equals(persist.getId())) {
                    hasField = true;
                    if (isCreate) continue;
                    if (!dataField.getFieldName().equalsIgnoreCase(persist.getFieldName())
                            || !dataField.getFieldType().equalsIgnoreCase(persist.getFieldType())
                            || !dataField.getRequired().equalsIgnoreCase(persist.getRequired())
                            || (dataField.getFieldSize() == null && persist.getFieldSize() != null)
                            || (dataField.getFieldSize() != null
                                && !dataField.getFieldSize().equals(persist.getFieldSize()))
                            || (DataFieldBO.FIELD_TYPE_NUMERIC.equalsIgnoreCase(dataField.getFieldType())
                                && !Objects.equals(dataField.getFieldPrecision(), persist.getFieldPrecision()))) {
                        updateDmField(tableName, dataField, persist);
                    }
                }
            }
            for(DataFieldBO dataField : bo.getFieldList()) {
                if (Tool.isNotBlank(dataField.getId())
                        && persist.getFieldName().equalsIgnoreCase(dataField.getFieldName())) {
                    hasField = true;
                    break;
                }
            }

            if (!hasField) {
                dataFieldDao.delete(persist);
                dataFieldRelevanceService.deleteByFieldId(persist.getId());
            }
        }

        int index = 0;
        for (DataFieldBO field : bo.getFieldList()) {
            field.setModelId(bo.getId());
            if (Tool.isBlank(field.getBuiltin())) {
                field.setBuiltin(Constants.NO);
            }
            if (Tool.isBlank(field.getPrimaryKey())) {
                field.setPrimaryKey(Constants.NO);
            }
            if (!Constants.YES.equals(field.getBuiltin())) {
                field.setOrdinal(++index);
            }
            field.setUpdateId(bo.getUpdateId());
            field.setCreateId(bo.getCreateId());
            this.save(field);
        }

        for (DataFieldBO field : bo.getFieldList()) {
            if (field.getNativeFieldId() != null && field.getNativeFieldId().startsWith("temp_")) {
                Integer fieldFlag = Integer.parseInt(field.getNativeFieldId().substring("temp_".length()));
                bo.getFieldList().stream()
                        .filter(item -> item.getFieldFlag() != null && item.getFieldFlag().equals(fieldFlag))
                        .findFirst().ifPresent(item -> {
                            field.setNativeFieldId(item.getId());
                            save(field);
                        });
            }
        }

        // 修改索引
        updateIndex(bo);
        return 1;
    }

    private void checkField(DataModelBO bo) {
        for (DataFieldBO dataField : bo.getFieldList()) {
            if (Constants.YES.equals(dataField.getBuiltin())) continue;
            if (Tool.isBlank(dataField.getFieldName())) throw new CommonException("字段名不能为空！");

            if (dataField.getFieldName().trim().toLowerCase().endsWith("_name")) {
                throw new CommonException("字段名称不能以[_name]结尾！");
            }

            if (reserveFieldList.contains(dataField.getFieldName().toLowerCase())) {
                throw new CommonException("字段名称不能为[" + dataField.getFieldName() + "]！");
            }

            if (defaultFieldMap.containsKey(dataField.getFieldName().toLowerCase())) {
                throw new CommonException("字段名称不能为[" + dataField.getFieldName() + "]！");
            }

            if (dataField.getFieldName().matches(".*([\"'`~,\\-=!@#$%^&*(){}\\[\\]\\\\]).*")) {
                throw new CommonException("字段名称不能包含特殊字符['\"`~,.\\-=!@#$%^&*|\\\\(){}\\[\\]]！");
            }
            dataField.setFieldName(dataField.getFieldName().trim().toLowerCase());
        }
    }

    @WriteTransactional
    public int saveView(DataModelBO bo) {
        for (DataFieldBO dataField : bo.getFieldList()) {
            if (Constants.YES.equals(dataField.getBuiltin())) continue;
            if (Tool.isBlank(dataField.getFieldName())) throw new CommonException("字段名不能为空！");
        }

        // 删除多余/修改变动的字段
        DataFieldBO qo = new DataFieldBO();
        qo.setModelId(bo.getId());
        qo.setBuiltin(Constants.NO);
        List<DataFieldBO> dataFieldList = dataFieldDao.queryList(qo);

        for (DataFieldBO persist : dataFieldList) {
            boolean hasField = false;
            for(DataFieldBO dataField : bo.getFieldList()) {
                if (Tool.isNotBlank(dataField.getId()) && dataField.getId().equals(persist.getId())) {
                    hasField = true;
                }
            }
            for(DataFieldBO dataField : bo.getFieldList()) {
                if (persist.getFieldName().equalsIgnoreCase(dataField.getFieldName())) {
                    hasField = true;
                    break;
                }
            }

            if (!hasField) {
                dataFieldDao.delete(persist);
                dataFieldRelevanceService.deleteByFieldId(persist.getId());
            }
        }

        int index = 0;
        for (DataFieldBO field : bo.getFieldList()) {
            field.setModelId(bo.getId());
            if (Tool.isBlank(field.getBuiltin())) {
                field.setBuiltin(Constants.NO);
            }
            if (Tool.isBlank(field.getPrimaryKey())) {
                field.setPrimaryKey(Constants.NO);
            }
            if (!Constants.YES.equals(field.getBuiltin())) {
                field.setOrdinal(++index);
            }
            field.setUpdateId(bo.getUpdateId());
            field.setCreateId(bo.getCreateId());

            // 先保存非派生字段，以使用获取原生字段的id
            if (!DataFieldBO.FIELD_TYPE_DERIVE.equals(field.getFieldType())) {
                this.save(field);
            }
        }

        // 保存派生字段
        for (DataFieldBO field : bo.getFieldList()) {
            if (Tool.isNotBlank(field.getNativeFieldId()) && field.getNativeFieldId().length() < 20) {
                bo.getFieldList().stream()
                        .filter(item -> field.getNativeFieldId().equals(item.getFieldFlag().toString()))
                        .findFirst()
                        .ifPresent(item -> field.setNativeFieldId(item.getId()));
            }

            this.save(field);
        }

        return 1;
    }

    @WriteTransactional
    public int saveLocalTable(DataModelBO bo) {
        int index = 0;
        for (DataFieldBO field : bo.getFieldList()) {
            field.setModelId(bo.getId());
            if (Tool.isBlank(field.getBuiltin())) {
                field.setBuiltin(Constants.NO);
            }
            if (Tool.isBlank(field.getPrimaryKey())) {
                field.setPrimaryKey(Constants.NO);
            }
            if (!Constants.YES.equals(field.getBuiltin())) {
                field.setOrdinal(++index);
            }
            field.setUpdateId(bo.getUpdateId());
            field.setCreateId(bo.getCreateId());
            this.save(field);
        }

        for (DataFieldBO field : bo.getFieldList()) {
            if (field.getNativeFieldId() != null && field.getNativeFieldId().startsWith("temp_")) {
                Integer fieldFlag = Integer.parseInt(field.getNativeFieldId().substring("temp_".length()));
                bo.getFieldList().stream()
                        .filter(item -> item.getFieldFlag() != null && item.getFieldFlag().equals(fieldFlag))
                        .findFirst().ifPresent(item -> {
                            field.setNativeFieldId(item.getId());
                            save(field);
                        });
            }
        }
        return 1;
    }

    private boolean isEqualsLength(DataFieldBO dataFieldBO, Map<String, Object> column) {
        String[] noFieldSize = new String[] { "整数", "时间", "图片", "视频", "附件", "联动" };
        if (Arrays.stream(noFieldSize).anyMatch(item -> item.equals(dataFieldBO.getFieldType()))) {
            return true;
        } else if (DataFieldBO.FIELD_TYPE_NUMERIC.equals(dataFieldBO.getFieldType())) {
            return (dataFieldBO.getFieldSize() != null
                    && dataFieldBO.getFieldSize().equals(column.get("precision")))
                    && dataFieldBO.getFieldPrecision() != null
                    && dataFieldBO.getFieldPrecision().equals(column.get("scale"));
        } else {
            return dataFieldBO.getFieldSize() != null && dataFieldBO.getFieldSize().equals(column.get("length"));
        }
    }

    @WriteTransactional
    public void saveList(List<DataFieldBO> list) {
        if (list == null) return;

        list.forEach(item -> {
            if (dataFieldDao.queryBy(item.getId()) != null) {
                item.setUpdateTime(System.currentTimeMillis());
                dataFieldDao.update(item);
            } else {
                item.setUpdateTime(System.currentTimeMillis());
                item.setCreateTime(System.currentTimeMillis());
                dataFieldDao.insert(item);
            }

            CacheUtil.remove(CacheKey.DataField.MODEL_LIST_PREFIX + item.getModelId());
        });
    }

    private void addBaseField(String modelId, List<DataFieldBO> fieldList) {
        defaultFieldMap.forEach((key, value) -> {
            if(fieldList.stream().noneMatch(item -> key.equalsIgnoreCase(item.getFieldName()))) {
                fieldList.add(value.cloneValue(modelId));
            }
        });
    }

    @WriteTransactional
    public void createDmTable(String tableName, List<DataFieldBO> fieldList) {
        TableVO tableVO = new TableVO();
        tableVO.setName(tableName);
        fieldList.stream().filter(item -> !DataFieldBO.FIELD_TYPE_DERIVE.equals(item.getFieldType())).forEach(field -> {
            FieldVO fieldVO = new FieldVO();
            fieldVO.setName(field.getFieldName());
            fieldVO.setType(field.getFieldType());
            fieldVO.setPrimaryKey(field.getPrimaryKey());
            fieldVO.setLength(field.getFieldSize() == null ? null : field.getFieldSize().longValue());
            fieldVO.setPrecision(field.getFieldPrecision());
            fieldVO.setNullable(Constants.YES.equals(field.getRequired()) ? Constants.NO : Constants.YES);
            fieldVO.setOrdinal(field.getOrdinal() == null ? 80 : field.getOrdinal());
            tableVO.getFieldList().add(fieldVO);
        });
        for (String ddl : getResolver().createTableDDL(tableVO)) {
            commonService.executeDDL(ddl);
        }
    }

    @WriteTransactional
    public void addDataField(String modelId, List<DataFieldBO> fieldList) {
        DataModelBO dataModel = dataModelDao.queryBy(modelId);
        if (dataModel == null) return;

        DataFieldBO qo = new DataFieldBO();
        qo.setModelId(modelId);
        qo.setBuiltin(Constants.YES);
        List<DataFieldBO> dataFieldList = dataFieldDao.queryList(qo);
        fieldList.forEach(field -> {
            if (dataFieldList.stream().anyMatch(item -> item.getFieldName().equals(field.getFieldName()))) return;

            this.save(field.cloneValue(modelId));
            try {
                this.addDmField(dataModel.getCode(), field);
            } catch (Exception ignored) {}
        });
    }

    @WriteTransactional
    public void addDmField(String tableName, DataFieldBO dataField) {
        FieldVO field = new FieldVO();
        field.setName(dataField.getFieldName());
        field.setType(dataField.getFieldType());
        field.setPrimaryKey(dataField.getPrimaryKey());
        field.setLength(dataField.getFieldSize() == null ? null : dataField.getFieldSize().longValue());
        field.setPrecision(dataField.getFieldPrecision());
        field.setNullable(Constants.YES.equals(dataField.getRequired()) ? Constants.NO : Constants.YES);
        this.commonService.executeDDL(getResolver().addFieldDDL(tableName, field));
    }

    @WriteTransactional
    public void updateDmField(String tableName, DataFieldBO newDataField, DataFieldBO oldDataField) {
        FieldVO newField = new FieldVO();
        newField.setName(newDataField.getFieldName());
        newField.setType(newDataField.getFieldType());
        newField.setPrimaryKey(newDataField.getPrimaryKey());
        newField.setLength(newDataField.getFieldSize() == null ? null : newDataField.getFieldSize().longValue());
        newField.setPrecision(newDataField.getFieldPrecision());
        newField.setNullable(Constants.YES.equals(newDataField.getRequired()) ? Constants.NO : Constants.YES);

        FieldVO oldField = new FieldVO();
        oldField.setName(oldDataField.getFieldName());
        oldField.setType(oldDataField.getFieldType());
        oldField.setPrimaryKey(oldDataField.getPrimaryKey());
        oldField.setLength(oldDataField.getFieldSize() == null ? null : oldDataField.getFieldSize().longValue());
        oldField.setPrecision(oldDataField.getFieldPrecision());
        oldField.setNullable(Constants.YES.equals(oldDataField.getRequired()) ? Constants.NO : Constants.YES);

        for (String ddl : getResolver().modifyFieldDDL(tableName, newField, oldField)) {
            this.commonService.executeDDL(ddl);
        }
    }

    @WriteTransactional
    public void updateIndex(DataModelBO bo) {
        Map<String, TableVO> tableMap = new HashMap<>();
        tableMap.put(bo.getCode(), new TableVO());
        IResolver resolver = getResolver();
        resolver.parseIndex(tableMap, commonService.queryList(resolver.tableIndexSQL(bo.getCode())));
        TableVO table = tableMap.get(bo.getCode());
        if (table.getIndexList().isEmpty() && bo.getIndexList().isEmpty()) return;
        if (bo.getIndexList().isEmpty()) {
            table.getIndexList().forEach(index ->
                    commonService.executeDDL(resolver.dropIndexDDL(bo.getCode(), index.getName())));
        }

        List<IndexVO> indexList = new ArrayList<>();
        bo.getIndexList().stream()
                .collect(Collectors.groupingBy(DataIndexBO::getIndexName))
                .forEach((key, value) -> {
                    IndexVO indexVO = new IndexVO();
                    indexVO.setName(bo.getCode() + "_" + key);
                    value.forEach(field -> {
                        DataFieldBO dataFieldBO = bo.getFieldList().stream()
                                .filter(item -> item.getId().equals(field.getIndexFieldId()))
                                .findFirst()
                                .orElse(null);
                        if (dataFieldBO == null) return;
                        IndexFieldVO fieldVO = new IndexFieldVO();
                        fieldVO.setName(dataFieldBO.getFieldName());
                        fieldVO.setOrderType(field.getIndexOrdinal());
                        fieldVO.setOrdinal(field.getOrdinal());
                        indexVO.addField(fieldVO);
                        indexVO.setUnique(field.getIndexUnique());
                    });
                    indexList.add(indexVO);
                });
        table.getIndexList().forEach(index -> {
            if (hasNotIndex(index, indexList)) {
                // 无此索引，删除索引
                commonService.executeDDL(resolver.dropIndexDDL(bo.getCode(), index.getName()));
            }
        });

        indexList.forEach(index -> {
            if (hasNotIndex(index, table.getIndexList())) {
                // 无此索引，新建索引
                // commonService.executeDDL(resolver.dropIndexDDL(bo.getCode(), index.getName()));
                commonService.executeDDL(resolver.addIndexDDL(bo.getCode(), index));
            }
        });
    }

    private boolean hasNotIndex(IndexVO index, List<IndexVO> indexList) {
        for (IndexVO indexVO : indexList) {
            if (index.getName().equals(indexVO.getName()) && index.isUnique() == indexVO.isUnique()) {
                index.getFieldList().sort(Comparator.comparingInt(IndexFieldVO::getOrdinal));
                indexVO.getFieldList().sort(Comparator.comparingInt(IndexFieldVO::getOrdinal));
                String index1 = index.getFieldList().stream()
                        .map(item -> item.getName() + (item.getOrderType() == null ? "" : item.getOrderType()))
                        .collect(Collectors.joining());
                String index2 = indexVO.getFieldList().stream()
                        .map(item -> item.getName() + (item.getOrderType() == null ? "" : item.getOrderType()))
                        .collect(Collectors.joining());
                if (index1.equals(index2)) {
                    return false;
                }
            }
        }
        return true;
    }

    @WriteTransactional
    public int delete(DataFieldBO bo) {
        dataFieldRelevanceService.deleteByFieldId(bo.getId());
        CacheUtil.remove(CacheKey.DataField.MODEL_LIST_PREFIX + bo.getModelId());
        return dataFieldDao.delete(bo);
    }

    @WriteTransactional
    public void deleteByModelId(String tableName, String modelId) {
        if (Tool.isBlank(tableName) || Tool.isBlank(modelId)) return;

        DataFieldBO qo = new DataFieldBO();
        qo.setModelId(modelId);
        this.dataFieldDao.queryList(qo).forEach(this::delete);
        if (commonService.queryTotal(getResolver().tableTotalSQL(tableName)) > 0) {
            commonService.executeDDL(getResolver().dropTableDDL(tableName));
        }
        CacheUtil.remove(CacheKey.DataField.MODEL_LIST_PREFIX + modelId);
    }

    public IResolver getResolver() {
        return ResolverFactory.getResolver(DatabaseUtil.getType());
    }

    public String queryFieldType(String modelId, String fieldName) {
        DataFieldBO dataField = dataFieldDao.queryByFieldName(modelId, fieldName);
        return dataField == null ? null : dataField.getFieldType();
    }
}
